/**
 * <p>Title: JdbcUtils.java</p>
 * <p>Description: JDBC封装utils</p>
 * <p>Company: www.huoyunren.com</p>
 * @author duanxiangyun
 * @date 2015年1月7日
 * @version 1.0
 */
package com.duan.test.utils.common;

import java.io.IOException;
import java.io.InputStreamReader;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.Logger;

/**
 * jdbc基础封装类
 */
public class JdbcUtils {

	private Connection connection;
	private PreparedStatement pstmt;
	private ResultSet resultSet;
	private static Logger log = Logger.getLogger(JdbcUtils.class);
	private Properties prop = new Properties();

	public JdbcUtils(String profilename) {
		InputStreamReader in = null;
		try{
			in = new InputStreamReader(this.getClass().getClassLoader().getResourceAsStream(
					profilename), "UTF-8");
			prop.load(in);
		}
		catch (Exception e) {
			System.out.println("找不到配置文件"+profilename);
			e.printStackTrace();
		} finally {
			try {
				in.close();

			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		getConnection();
	}
	
	public JdbcUtils(Properties pro) {
		this.prop = pro;
		getConnection();
	}
	
	public JdbcUtils(Connection conn) {
		setConnection(conn);
	}
	
	public JdbcUtils(String driver,String url, String username, String password) {
		Connection conn = connect(driver, url, username, password);
		setConnection(conn);
	}
	/**
	 * 获取当前连接
	 * @return
	 */
	public Connection getConn() {
		return connection;
	}
	
	/**
	 * 连接数据库
	 * @param driver
	 * @param url
	 * @param username
	 * @param password
	 * @return
	 */
	private Connection connect (String driver,String url, String username, String password) {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 通过配置文件创建数据源
	 * @param profilename
	 * @return 
	 */
	private Connection getConnection () {	
		try {		
			String driver = prop.getProperty("driverClassName");
			String url = prop.getProperty("url");
			String username = prop.getProperty("username");
			String password = prop.getProperty("password");
			if (driver.isEmpty()) {
				Class.forName("com.mysql.jdbc.Driver");
			}
			else {
				Class.forName(driver);
			}
			Connection conn = DriverManager.getConnection(url, username, password);
			connection = conn;
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return connection;
	}


	/**
	 * 设置连接
	 * 
	 * @return Connection
	 */
	public void setConnection(Connection conn) {
		this.connection = conn;
	}

	/**
	 * 获取数据库命令执行对象的方法
	 * 
	 * @param sql
	 *            要执行的SQL命令拼装语句字符串
	 * @return 数据库命令执行对象
	 */
	private PreparedStatement getPreparedStatement(String sql) {
		try {
			// 根据获取的数据库连接对象创建数据库 命令执行对象
			if (connection == null) {
				connection = getConnection();
			}
			this.pstmt = connection.prepareStatement(sql);
		} catch (Exception ex) {
			System.err.println("获取数据库命令执行对象错误：" + ex.getMessage());
			// 在控制台输出异常堆栈信息
			// ex.printStackTrace();
		}
		// 返回数据库命令执行对象
		return this.pstmt;
	}

	/**
	 * 增加、删除、改
	 * 
	 * @param sql
	 * @param params
	 *            动态执行sql需要传入的参数列表
	 * @return boolean
	 * @throws SQLException
	 */
	public boolean updateByPreparedStatement(String sql, List<Object> params)
			throws SQLException {
		boolean flag = false;
		int result = -1;
		pstmt = getPreparedStatement(sql);
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		result = pstmt.executeUpdate();
		flag = result > 0 ? true : false;
		return flag;
	}

	/**
	 * 获得最新插入数据的id号
	 * 
	 * @param sql
	 * @return long
	 * @throws SQLException
	 */
	public long getInsertLastId(String sql) throws SQLException {
		if (connection == null) {
			connection = getConnection();
		}
		long ret = 0;
		PreparedStatement state = connection.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);
		ResultSet rs = null;
		state.executeUpdate();
		rs = state.getGeneratedKeys();
		if (rs.next()) {
			ret = rs.getLong(1);
		}
		return ret;
	}

	/**
	 * 静态查询查询单条记录
	 * 
	 * @param sql
	 *            sql语句
	 * @return Map<String, Object>
	 * @throws SQLException
	 */
	public Map<String, Object> findSimpleResult(String sql) throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		Statement stmt = connection.createStatement();
		resultSet = stmt.executeQuery(sql);// 返回查询结果
		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();
		while (resultSet.next()) {
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
		}
		return map;
	}

	/**
	 * 动态查询单条记录
	 * 
	 * @param sql
	 *            SQL语句
	 * @param params
	 *            动态参数值
	 * @return Map<String, Object>
	 * @throws SQLException
	 */
	public Map<String, Object> findSimpleResult(String sql, List<Object> params)
			throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		int index = 1;
		pstmt = getPreparedStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();// 返回查询结果
		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();
		while (resultSet.next()) {
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
		}
		return map;
	}

	/**
	 * 动态查询多条记录
	 * 
	 * @param sql
	 *            SQL语句
	 * @param params
	 *            动态参数值
	 * @return Map<String, Object>
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findModeResult(String sql,
			List<Object> params) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		int index = 1;
		pstmt = getPreparedStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				// col_map.put(i+1, cols_name);
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}

		return list;
	}

	/**
	 * 动态查询多条记录:根据SQL AS的值
	 * 
	 * @param sql
	 *            SQL语句
	 * @param params
	 *            动态参数值
	 * @return Map<String, Object>
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findModeResultForLabel(String sql,
			List<Object> params) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		int index = 1;
		pstmt = getPreparedStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnLabel(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				// col_map.put(i+1, cols_name);
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}

		return list;
	}

	/**
	 * 静态查询多条记录
	 * 
	 * @param sql
	 *            SQL语句
	 * @return Map<String, Object>
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findModeResult(String sql)
			throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		// pstmt = getPreparedStatement(sql);
		// resultSet = pstmt.executeQuery();
		Statement stmt = connection.createStatement();
		resultSet = stmt.executeQuery(sql);// 返回查询结果
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}

		return list;
	}

	/**
	 * 动态查询sql数组的多条记录
	 * 
	 * @param sql
	 *            SQL语句
	 * @param params
	 *            动态参数值
	 * @return Map<String, Object>
	 * @throws SQLException
	 */
	public List<List<Map<String, Object>>> findModeResults(String sql,
			List<Object> params) throws SQLException {
		List<List<Map<String, Object>>> list = new ArrayList<List<Map<String, Object>>>();
		int index = 1;
		pstmt = getPreparedStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		String[] sqls = (pstmt.toString().split(":"))[1].trim().split(";");
		for (int j = 0; j < sqls.length; j++) {
			list.add(j, findModeResult(sqls[j]));
		}
		return list;
	}

	/**
	 * 通过反射机制查询单条记录
	 * 
	 * @param sql
	 *            SQL语句
	 * @param params
	 *            动态参数值
	 * @param cls
	 *            类名
	 * @return <T> T
	 * @throws Exception
	 */
	public <T> T findSimpleRefResult(String sql, List<Object> params,
			Class<T> cls) throws Exception {
		T resultObject = null;
		int index = 1;
		pstmt = getPreparedStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			// 通过反射机制创建一个实例
			resultObject = cls.newInstance();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				Field field = cls.getDeclaredField(cols_name);
				field.setAccessible(true); // 打开javabean的访问权限
				field.set(resultObject, cols_value);
			}
		}
		return resultObject;

	}

	/**
	 * 通过反射机制查询多条记录
	 * 
	 * @param sql
	 *            SQL语句
	 * @param params
	 *            动态参数值
	 * @param cls
	 *            类名
	 * @return <T> T
	 * @throws Exception
	 */
	public <T> List<T> findMoreRefResult(String sql, List<Object> params,
			Class<T> cls) throws Exception {
		List<T> list = new ArrayList<T>();
		int index = 1;
		pstmt = getPreparedStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			// 通过反射机制创建一个实例
			T resultObject = cls.newInstance();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				Field field = cls.getDeclaredField(cols_name);
				field.setAccessible(true); // 打开javabean的访问权限
				field.set(resultObject, cols_value);
			}
			list.add(resultObject);
		}
		return list;
	}

	/**
	 * 获取执行指定sql语句后的返回结果集的记录条数
	 * 
	 * @param sql
	 *            要执行的SQL命令拼装语句字符串
	 * @param params
	 * @return 查询结果得到的记录条数
	 */
	public int getResultSetCount(String sql, List<Object> params) {
		// 保存得到指定的sql语句执行后返回记录行数的计数器变量
		int count = 0;
		try {
			// 置空结果集对象的原有内容
			this.resultSet = null;
			int index = 1;
			pstmt = getPreparedStatement(sql);
			if (params != null && !params.isEmpty()) {
				for (int i = 0; i < params.size(); i++) {
					pstmt.setObject(index++, params.get(i));
				}
			}
			resultSet = pstmt.executeQuery();// 返回查询结果
			// 遍历结果集并累加计数器
			while (this.resultSet.next()) {
				count++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * 获取执行指定sql语句后的返回结果集的记录条数
	 * 
	 * @param sql
	 *            要执行的SQL命令拼装语句字符串
	 * @return 查询结果得到的记录条数
	 */
	public int getResultSetCount(String sql) {
		// 保存得到指定的sql语句执行后返回记录行数的计数器变量
		int count = 0;
		try {
			// 置空结果集对象的原有内容
			this.resultSet = null;
			// 执行sql语句获得结果集
			this.resultSet = getPreparedStatement(sql).executeQuery();
			// 遍历结果集并累加计数器
			while (this.resultSet.next()) {
				count++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * 关闭数据库连接资源(包括结果集对象、命令执行对象、连 接对象)
	 */
	public void closeDBResource() {
		try {
			closeResultSet();
			closePreparedStatement();
			closeConnection();
		} catch (SQLException sqlEx) {
			// System.err.println(sqlEx.getMessage());
			// 在控制台输出异常堆栈信息
			sqlEx.printStackTrace();
		}
	}

	/**
	 * 关闭结果集对象的方法
	 * 
	 * @throws SQLException
	 */
	private void closeResultSet() throws SQLException {
		try {
			if (this.resultSet != null) {
				this.resultSet.close();
				this.resultSet = null;
			}
		} catch (SQLException sqlEx) {
			throw new SQLException("关闭结果集对 象错误：" + sqlEx.getMessage());
			// 在控制台输出异常堆栈信息
			// sqlEx.printStackTrace();
		}
	}

	/**
	 * 关闭数据库命令执行对象的方法
	 * 
	 * @throws SQLException
	 */
	private void closePreparedStatement() throws SQLException {
		try {
			if (this.pstmt != null) {
				this.pstmt.close();
				this.pstmt = null;
			}
		} catch (SQLException sqlEx) {
			throw new SQLException("关闭数据库命 令执行对象错误：" + sqlEx.getMessage());
			// 在控制台输出异常堆栈信息
			// sqlEx.printStackTrace();
		}
	}

	/**
	 * 关闭数据库连接的方法
	 * 
	 * @throws SQLException
	 */
	private void closeConnection() throws SQLException {
		try {
			if (this.connection != null && (!this.connection.isClosed())) {
				this.connection.close();
			}
		} catch (SQLException sqlEx) {
			throw new SQLException("关闭数据库连 接错误：" + sqlEx.getMessage());
			// 在控制台输出异常堆栈信息
			// sqlEx.printStackTrace();
		}
	}

	/**
	 * 提交事务
	 * 
	 * @param sql
	 * @return int
	 * @throws Exception
	 */
	public int commitwithtrans(String[] sql) {
		// System.out.println("提交语句:" + sql);	
		int result = 0;
		int total = sql.length;
		try {
			if (connection.getAutoCommit()) {
				connection.setAutoCommit(false);// 更改JDBC事务的默认提交方式
			}
			for (int i = 0; i < total; i++) {
				if (sql[i] != null && sql[i].length() > 0) {
					log.debug("提交语句:" + sql[i]);
					pstmt = connection.prepareStatement(sql[i]);
					result += pstmt.executeUpdate();
				}
			}
			connection.commit();// 提交JDBC事务
		} catch (SQLException e) {
			result = -1;
			e.printStackTrace();
			log.debug(e.getMessage());
			try {
				connection.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}// 恢复JDBC事务的默认提交方式
		}
		return result;
	}

	/**
	 * 提交事务
	 * @param sql
	 * @param autocommit 是否自动提交
	 * @return 事务成功数
	 */
	public int commitwithtrans(String[] sql,Boolean autocommit) {
		// System.out.println("提交语句:" + sql);	
		int result = 0;
		int total = sql.length;
		try {
			connection.setAutoCommit(autocommit);// 更改JDBC事务的默认提交方式
			for (int i = 0; i < total; i++) {
				if (sql[i] != null && sql[i].length() > 0) {
					log.debug("提交语句:" + sql[i]);
					pstmt = connection.prepareStatement(sql[i]);
					result += pstmt.executeUpdate();
				}
			}
			if (!autocommit) {
				connection.commit();// 提交JDBC事务
			}
		} catch (SQLException e) {
			result = -1;
			e.printStackTrace();
			log.debug(e.getMessage());
			try {
				connection.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}// 恢复JDBC事务的默认提交方式
		}
		return result;
	}

	/**
	 * 动态得到SQL
	 * 
	 * @param sql
	 * @param params
	 * @return String
	 */
	public String getPreparedSQL(String sql, String[] params) {
		int paramNum = 0;
		if (null != params)
			paramNum = params.length;
		if (1 > paramNum)
			return sql;
		StringBuffer returnSQL = new StringBuffer();
		String[] subSQL = sql.split(";");
		for (int i = 0; i < paramNum; i++) {
			returnSQL.append(subSQL[i]).append(" '").append(params[i])
					.append("' ");
		}
		if (subSQL.length > params.length) {
			returnSQL.append(subSQL[subSQL.length - 1]);
		}
		return returnSQL.toString();
	}

	/**
	 * 提交事务
	 * 
	 * @param coon
	 *            Connection对象
	 * @param procsql
	 *            提交事务sql语句
	 * @param paramValue
	 *            数组参数值
	 * @return String
	 * @throws Exception
	 */
	public String commitProc(Connection coon, String procsql,
			String[] paramValue) throws Exception {
		// System.out.println("提交语句:" + getPreparedSQL(procsql, paramValue));
		log.debug("提交语句:" + getPreparedSQL(procsql, paramValue));
		CallableStatement proc = null;
		String result = "0";
		StringBuffer sql = new StringBuffer("{ call " + procsql + "}");
		int paramLen = paramValue.length;
		try {
			proc = coon.prepareCall(sql.toString());
			if (proc != null) {
				for (int i = 0; i < paramLen; i++) {
					proc.setString(i + 1, paramValue[i]);// 设置输入参数的值
				}
				proc.registerOutParameter(paramLen + 1, Types.VARCHAR);// 注册输出参数为字符串
				proc.execute();
				result = proc.getString(paramLen + 1);// 取输出参数的值
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.debug(e.getMessage());
			result = "-1";
		} finally {
			if (proc != null)
				proc.close();
			if (coon != null)
				coon.close();
		}
		return result;
	}

	/**
	 * 
	 * @param sql
	 *            返回从from行起的rows条数据
	 * @param params
	 * @param from
	 *            开始行
	 * @param rows
	 *            显示记录条数
	 * @return List<Map<String, Object>>
	 * @throws Exception
	 */
	public List<Map<String, Object>> retrieveFromRows(String sql,
			List<Object> params, int from, int rows) throws Exception {
		String newsql = sql + " limit " + (from - 1) + "," + rows;
		return findModeResult(newsql, params);
	}

	/**
	 * 
	 * @param sql
	 *            返回前rows条数据
	 * @param params
	 * @param rows
	 *            前几条记录数
	 * @return List<Map<String, Object>>
	 * @throws Exception
	 */
	public List<Map<String, Object>> retrieveFirstFrewRows(String sql,
			List<Object> params, int rows) throws Exception {
		String newsql = sql + " limit " + rows;
		return findModeResult(newsql, params);
	}

	/**
	 * 
	 * @param sql
	 *            返回从from行起到最后一行的记录
	 * @param params
	 * @param from
	 *            开始行
	 * @return List<Map<String, Object>>
	 * @throws Exception
	 */
	public List<Map<String, Object>> retrieveFromRowToEnd(String sql,
			List<Object> params, int from) throws Exception {
		int tempNum = getResultSetCount(sql, params);
		String newsql = null;
		if (tempNum >= from) {
			newsql = sql + " limit " + (from - 1) + "," + (tempNum - from + 1);
		} else {
			log.error("开始行不能大于数据总行数");
			System.out.println("开始行不能大于数据总行数");
		}
		return findModeResult(newsql, params);
	}

	/**
	 * 
	 * @param sql
	 *            返回最后rows条记录
	 * @param params
	 * @param rows
	 *            后几条记录数
	 * @return List<Map<String, Object>>
	 * @throws Exception
	 */
	public List<Map<String, Object>> retrieveLastFrewRows(String sql,
			List<Object> params, int rows) throws Exception {
		String newsql = sql + " order by id desc limit " + rows;
		return findModeResult(newsql, params);
	}
	
	/**
	 * 静态查询多条记录
	 * 
	 * @param sql
	 *            SQL语句
	 * @return Map<String, String>
	 * @throws SQLException
	 */
	public List<Map<String, String>> findModeStringResult(String sql)
			throws SQLException {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		Statement stmt = connection.createStatement();
		resultSet = stmt.executeQuery(sql);// 返回查询结果
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map<String, String> map = new HashMap<String, String>();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				int type = metaData.getColumnType(i+1);
				Object value = resultSet.getObject(cols_name);
				String cols_value = null;
				if (value == null) {
					cols_value = "";
				}
				else if (type == Types.TIMESTAMP || type == Types.DATE) {
					SimpleDateFormat sm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					cols_value = sm.format(value);
				}
				else if (type == Types.TINYINT || type == Types.BIT) {
					int a = Types.BIT;
					if (value instanceof Boolean) {
						cols_value = ((Boolean)value == true ? "1":"0");
					}
					else {
						cols_value = String.valueOf(value);
					}
				}
				else if (type == Types.DECIMAL) {
					String s = value.toString();
					if(s.indexOf(".") > 0){
				        s = s.replaceAll("0+?$", "");//去掉多余的0
				        s = s.replaceAll("[.]$", "");//如最后一位是.则去掉
					}
					cols_value = s;
				}
				else {
					cols_value = value.toString();
				}
				
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}
		return list;
	}
}